Single-Sign-On via OAuth

One of the ways users can connect to data sources using their own access rights is via OAuth Authentication. This type of authentication utilizes the user's credentials to connect and authenticate access to a given data source directly. The process is often used in big organizations that have centralized security and are using one framework to secure all data assets.

It is possible to establish a Single-Sign-On framework using an OAuth provider (like Microsoft Azure Active Directory) that allows users to authenticate in Pyramid and then credential individually downstream to a data technology. Pyramid supports Single-Sign-On via OAuth for the following data sources:

Note: This OAuth option is currently only available for certain data sources: Snowflake, Azure Synapse, BigQuery, Box. Other SSO techniques are available for other database technologies like MS SQL Server, MS OLAP, SAP Hana and SAP BW.

Enabling OAuth for Data Access

Where relevant, the Security tab contains an Authentication Method drop down to select the type of connection to be employed with that particular connection instance. After the default Username and Password there are a few possible options for OAuth Single Sign On, depending on the data source:

  • Single Sign On (OAuth) - Specific User: This option will use the identity of a specific user to authenticate through identity provider, retrieve the OAuth security identity for that user, then connect to the data source as that user. All users will share the same identity when querying data.

  • Single Sign On (OAuth) - End User: (for Azure Synapse and Box) Each user will be prompted to sign into Azure Synapse and Box when starting Pyramid or when connecting to the data source. This is a “one off” event. The user’s sign in code will be stored and reused for subsequent data access. Pyramid will automatically refresh this as needed. All users will share the Client ID and Client Secret defined here.

  • Single Sign On (OAuth) - Proxy 1: (for Snowflake) As well as establishing Single sign On between the provider, Pyramid and the data source by individual user, this option will use the user name contained in the Proxy 1 user information field for onward connection to other data sources, for example MS OLAP or SAP BW.

  • Single Sign On (OAuth) - Proxy 2: (for Snowflake) As well as establishing Single sign On between the provider, Pyramid and the data source, by individual user, This option will use the user name contained in the Proxy 2 user information field for onward connection to other data sources, for example MS OLAP or SAP BW.

Details on setting up the proxy account can be found here.

Setting up the Provider in the Admin

Some of the 'global' settings can be setup in the Global Settings page, if you need to repeat them on multiple data cards.

When setting up the OAuth, the following details are required.

  • Client ID: Client ID identifier associated with the data application to be connected to.

  • Client Secret: Client Secret identifier associated with the data application to be connected to.

  • Scope: Data source string that can limit the operations and roles permitted by the access token and what the user can access in the data source

  • Custom OAuth Settings:

    • JSON Web Keys URI: The location of the Azure JSON Web Keys Set

    • OAuth Token Endpoint: Azure string used by Pyramid to get an access token or a refresh token

    • OAuth Authentication Endpoint:Azure string used by Pyramid to get an access token or a refresh token

Redirect

Pyramid requires a redirect page to define where the provider returns the OAuth tokens requested. Pyramid by default will use https://<pyramidservername>/AuthenticateCallbackPage, however, it must be set on the Global Settings page. A button there will populate the field with this value. You can also specify an alternate redirect page if required.

  • Click here for more details on BigQuery OAuth setups
  • Click here for more details on Box OAuth setups

User ID Flow

Specific User

The Proxy 1 and Proxy 2 user information fields can be used to inject alternative account names to be used with alternative system authentications. For example, the user's Active Directory account needed for Microsoft SSAS authentication, or the user's SAP BW login for onward connection in other Single Sign On environments, like Azure / Snowflake.

Shared User

For the "Single Sign On (OAuth) - Specific User" option, all users will be sharing the same Client ID, Client Secret and Scope, but will also share the same login connection to the data source. Supplying the User name and clicking on the Connect Button will connect to Snowflake, Azure Synapse, Google Big Query, or Box and return the OAuth Refresh code to be used by Pyramid to connect to Snowflake, Azure Synapse, Google Big Query, or Box .

  • Connect Button: Connect to Snowflake or Box and retrieve the Refresh Code.
  • User Name: Snowflake or Azure Synapse user name with which to connect.
  • OAuth Refresh Code: Refresh authorization code returned by Snowflake or Box.

User Experience

Prompts

When a user logs into Pyramid or attempts to access a data source authenticated through OAuth, then they will be prompted to connect to the relevant data source (like Snowflake) using their account details. This will be used to connect that user to their data, enabling user level data security (and effectively sharing the Client ID and Client Secret and Scope as entered).

Initial Login

The first time a user connects, a pop up will appear from the identity provider during the authentication of that user. This is by design from provider and Pyramid has no control over this. There may also be a small delay on first connecting to such data sources while the provider authenticates the user and generates the OAuth tokens needed. This delay will reoccur should the OAuth access tokens expire and re-authentication is required. The time interval before expiration is set by the provider administrators.